﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Helper;
using CoreData;
namespace SchoolManagementSystem.Report._3._KeToan
{
    class KeToanReportData
    {
        public static DataTable LoadStudentFeeBySearchString(string SearchString)
        {
            DataTable dt = new DataTable();
            var data = (List<Student_Fee>)RegisterService.RegisterServices.Student_FeeService.GetList(SearchString);
            if (data == null || data.Count <= 0)
            {
                data = new List<Student_Fee>();
            }
            var l = new KList<Student_Fee>();
            dt = l.ConvertToDataTable(data);
            return dt;
        }

        public static DataTable LoadStudentFeeBySearchStringWithStatus(string SearchString)
        {
            //trang thai hoc vien  //2: = bao luu, 4 dang hoc, 9 da tot nghiep,10 nghỉ luôn
            DataTable dt = new DataTable();
            var data = (List<Student_Fee>)RegisterService.RegisterServices.Student_FeeService.GetForReportData(SearchString);
            if (data == null || data.Count <= 0)
            {
                data = new List<Student_Fee>();
            }
           var DataTemp = new KList<Student_Fee>();
           int TongTien = 0;
           int SLBaoLuu = 0;
           int SLNhapHoc = 0;
           int SLTotNghiep = 0;
           int SLNghiLuon = 0;

           for (int i = 0; i < data.Count; i++)
           {
               TongTien = 0;
               SLBaoLuu = 0;
               SLNhapHoc = 0;
               SLTotNghiep = 0;
               SLNghiLuon = 0;
               if (data[i].IsProcessExt == false)
               {
                   TongTien += data[i].TotalMoneyExt;
                   SLBaoLuu += data[i].StudentSaveAmountExt;
                   SLNhapHoc += data[i].ClassSizeExt;
                   SLTotNghiep += data[i].StudentOutPutAmountExt;
                   SLNghiLuon += data[i].StudentOffAmountExt;
                   for (int j = 1; j < data.Count; j++)
                   {
                       if ((i!=j)&&(data[i].ClassNameExt == data[j].ClassNameExt))
                       {
                           if (!data[j].IsProcessExt)
                           {
                               TongTien += data[j].TotalMoneyExt;
                               data[j].IsProcessExt = true;
                              
                                   if (data[j].StudentStatusExt == 2) //bao luu
                                   {
                                       SLBaoLuu += data[j].StudentSaveAmountExt;
                                   }

                                   if (data[j].StudentStatusExt == 9) //tot nghiep
                                   {
                                       SLTotNghiep += data[j].StudentOutPutAmountExt;
                                   }
                                   if (data[j].StudentStatusExt == 10) //nghi lun
                                   {
                                       SLNghiLuon += data[j].StudentOffAmountExt;
                                   }
                               
                           }
                       }
                   }
                   data[i].IsProcessExt = true;
                   data[i].TotalMoneyExt = TongTien;
                   data[i].StudentOffAmountExt = SLNghiLuon;
                   data[i].StudentOutPutAmountExt = SLTotNghiep;
                   data[i].StudentSaveAmountExt = SLBaoLuu;
                   data[i].StudentInPutAmountExt = SLNhapHoc;
                   DataTemp.Add(data[i]);
               }
           }

           dt = DataTemp.ConvertToDataTable(DataTemp);
            return dt;
        }

        public static DataTable LoadStudentPayFee(string SearchString)
        {
            DataTable dt = new DataTable();
            var data = (List<Student_Fee>)RegisterService.RegisterServices.Student_FeeService.GetStudentPayFeeForReportData(SearchString);
            if (data == null || data.Count <= 0)
            {
                data = new List<Student_Fee>();
            }

            var DataTemp = new KList<Student_Fee>();

            foreach (var item in data)
            {
                var voucherfee = RegisterService.RegisterServices.ReceiptVoucherFeeService.GetByStudentID(item.StudentID);
                if (voucherfee != null && voucherfee.Count != 0)
                {
                    if (item.TypeExt == 1)
                    {
                        try
                        {
                            item.Money1Ext = voucherfee.Where(c => c.TimeFee.Contains("Lần 1")).Sum(c => c.Money);
                        }
                        catch { item.Money1Ext = 0; }
                    }
                    if (item.TypeExt == 2)
                    {
                        try
                        {
                            item.Money1Ext = voucherfee.Where(c => c.TimeFee.Contains("Lần 1")).Sum(c => c.Money);
                        }
                        catch { item.Money1Ext = 0; }
                        try {
                            item.Money2Ext = voucherfee.Where(c => c.TimeFee.Contains("Lần 2")).Sum(c => c.Money);
                        }
                        catch {
                            item.Money2Ext = 0;
                        }
                    }
                    if (item.TypeExt == 4)
                    {
                        try
                        {
                            item.Money1Ext = voucherfee.Where(c => c.TimeFee.Contains("Lần 1")).Sum(c => c.Money);
                        }
                        catch {
                            item.Money1Ext = 0;
                        }
                        try
                        {
                            item.Money2Ext = voucherfee.Where(c => c.TimeFee.Contains("Lần 2")).Sum(c => c.Money);
                        }
                        catch
                        {
                            item.Money2Ext = 0;
                        }
                        try
                        {
                            item.Money3Ext = voucherfee.Where(c => c.TimeFee.Contains("Lần 3")).Sum(c => c.Money);
                        }
                        catch
                        {
                            item.Money3Ext = 0;
                        }
                        try
                        {
                            item.Money4Ext = voucherfee.Where(c => c.TimeFee.Contains("Lần 4")).Sum(c => c.Money);
                        }
                        catch
                        {
                            item.Money4Ext = 0;
                        }
                    }
                    if (item.TypeExt == 6)
                    {
                        try
                        {
                            item.Money1Ext = voucherfee.Where(c => c.TimeFee.Contains("Lần 1")).Sum(c => c.Money);
                        }
                        catch { item.Money1Ext = 0; }
                        try
                        {
                            item.Money2Ext = voucherfee.Where(c => c.TimeFee.Contains("Lần 2")).Sum(c => c.Money);
                        }
                        catch
                        {
                            item.Money2Ext = 0;
                        }
                        try
                        {
                            item.Money3Ext = voucherfee.Where(c => c.TimeFee.Contains("Lần 3")).Sum(c => c.Money);
                        }
                        catch
                        {
                            item.Money3Ext = 0;
                        }
                        try
                        {
                            item.Money4Ext = voucherfee.Where(c => c.TimeFee.Contains("Lần 4")).Sum(c => c.Money);
                        }
                        catch
                        {
                            item.Money4Ext = 0;
                        }
                        try
                        {
                            item.Money5Ext = voucherfee.Where(c => c.TimeFee.Contains("Lần 5")).Sum(c => c.Money);
                        }
                        catch
                        {
                            item.Money5Ext = 0;
                        }
                        try
                        {
                            item.Money6Ext = voucherfee.Where(c => c.TimeFee.Contains("Lần 5")).Sum(c => c.Money);
                        }
                        catch
                        {
                            item.Money6Ext = 0;
                        }
                    }
                }
            }
            return DataTemp.ConvertToDataTable(data);

        }



        public static DataTable LoadStudentFeeByStartDateEndDate(DateTime Start, DateTime End, string SearchString,out long totalHPphaithu, out long totalHPdathu, out long totalHPNo)
        {

            totalHPNo = totalHPdathu = totalHPphaithu = 0;
            var data = RegisterService.RegisterServices.VwStudentFeeService.GetListReturnDataTable(SearchString);
            var datalist = new KList<VwStudentFee>();
            var result = new DataTable();

            if(data!=null && data.Rows.Count>0)
            {


                for (int i = 0; i <= data.Rows.Count-1;i++ )
                {

                    
                    #region Tinh toan
                    DateTime StartDate1, StartDate2, StartDate3, StartDate4, StartDate5, StartDate6;
                    StartDate1 = DateTime.MinValue; StartDate2 = DateTime.MinValue; StartDate3 = DateTime.MinValue;
                    StartDate4 = DateTime.MinValue; StartDate5 = DateTime.MinValue; StartDate6 = DateTime.MinValue;

                    DateTime EndDate1, EndDate2, EndDate3, EndDate4, EndDate5, EndDate6;

                    EndDate1 = DateTime.MinValue; EndDate2 = DateTime.MinValue; EndDate3 = DateTime.MinValue;
                    EndDate4 = DateTime.MinValue; EndDate5 = DateTime.MinValue; EndDate6 = DateTime.MinValue;

                    if (data.Rows[i]["StartDate1"] != DBNull.Value)
                        StartDate1 = DateTime.Parse(data.Rows[i]["StartDate1"].ToString());
                    if (data.Rows[i]["StartDate2"] != DBNull.Value)
                        StartDate2 = (DateTime)data.Rows[i]["StartDate2"];
                    if (data.Rows[i]["StartDate3"] != DBNull.Value)
                        StartDate3 = DateTime.Parse(data.Rows[i]["StartDate3"].ToString());
                    if (data.Rows[i]["StartDate4"] != DBNull.Value)
                        StartDate4 = DateTime.Parse(data.Rows[i]["StartDate4"].ToString());
                    if (data.Rows[i]["StartDate5"] != DBNull.Value)
                        StartDate5 = DateTime.Parse(data.Rows[i]["StartDate5"].ToString());
                    if (data.Rows[i]["StartDate6"] != DBNull.Value)
                        StartDate6 = DateTime.Parse(data.Rows[i]["StartDate6"].ToString());
                    if (data.Rows[i]["EndDate1"] != DBNull.Value)
                        EndDate1 = DateTime.Parse(data.Rows[i]["EndDate1"].ToString());
                    if (data.Rows[i]["EndDate2"] != DBNull.Value)
                        EndDate2 = DateTime.Parse(data.Rows[i]["EndDate2"].ToString());
                    if (data.Rows[i]["EndDate3"] != DBNull.Value)
                        EndDate3 = DateTime.Parse(data.Rows[i]["EndDate3"].ToString());
                    if (data.Rows[i]["EndDate4"] != DBNull.Value)
                        EndDate4 = DateTime.Parse(data.Rows[i]["EndDate4"].ToString());
                    if (data.Rows[i]["EndDate5"] != DBNull.Value)
                        EndDate5 = DateTime.Parse(data.Rows[i]["EndDate5"].ToString());
                    if (data.Rows[i]["EndDate6"] != DBNull.Value)
                        EndDate6 = DateTime.Parse(data.Rows[i]["EndDate6"].ToString());


                    if(StartDate1>=Start && EndDate1 <= End)
                    {
                        var temp = new VwStudentFee();
                        temp.ID = long.Parse(data.Rows[i]["ID"].ToString());
                        temp.ClassType = int.Parse(data.Rows[i]["ClassType"].ToString());
                        temp.TypeOfFee = int.Parse(data.Rows[i]["TypeOfFee"].ToString());
                        temp.Name = data.Rows[i]["Name"].ToString();
                        var status = int.Parse(data.Rows[i]["StudentStatus"].ToString());
                        if (status == 10)
                        {
                            var dateOff = DateTime.MinValue;
                            if (data.Rows[i]["DateOfOff"] != DBNull.Value)
                                dateOff =DateTime.Parse(data.Rows[i]["DateOfOff"].ToString());
                            temp.Name += " (Nghỉ luôn " + dateOff.ToShortDateString() + ")";
                        }
                        else if (status == 2)
                        {
                            var dateOfReserved = DateTime.MinValue;
                            if (data.Rows[i]["DateOfReserved"] != DBNull.Value)
                              dateOfReserved=  DateTime.Parse(data.Rows[i]["DateOfReserved"].ToString());
                            temp.Name += " (Bảo lưu " + dateOfReserved.ToShortDateString() + ")";
                        }
                        else if (status == 9)
                        {
                            var dateOfCompleted = DateTime.MinValue;
                            if (data.Rows[i]["DateOfCompleted"] != DBNull.Value)
                              dateOfCompleted=  DateTime.Parse(data.Rows[i]["DateOfCompleted"].ToString());
                            temp.Name += " (Đã tốt nghiệp " + dateOfCompleted.ToShortDateString() + ")";
                        }
                        temp.StartDate = StartDate1;
                        temp.EndDate = EndDate1;
                        temp.ClassName = data.Rows[i]["ClassName"].ToString();
                        temp.CourseName = data.Rows[i]["CourseName"].ToString();
                        temp.BranchName = data.Rows[i]["BranchName"].ToString();
                        temp.MoneyPay = int.Parse(data.Rows[i]["Money1"].ToString()); //tien phai dong
                       
                        //lay phieu thu hoc phi
                        var receiptFee = RegisterService.RegisterServices.ReceiptVoucherFeeService.GetByStudentID(temp.ID);
                        if(receiptFee!=null && receiptFee.Count>0)
                        {
                            temp.MoneyPaid = receiptFee.Where(c => c.TimeFee.Contains("Lần 1")).Sum(c=>c.Money);
                        }

                        if (temp.MoneyPaid >= temp.MoneyPay)
                        {
                            temp.MoneyNotPaid = 0;
                        }
                        else
                        {
                            temp.MoneyNotPaid = temp.MoneyPay - temp.MoneyPaid;
                        }
                        //nếu trạng thái là nghỉ luôn thì nợ =0
                        if (status == 10)
                        {
                            temp.MoneyNotPaid = 0;
                        }
                        datalist.Add(temp);
                        
                    }

                    if (StartDate2 >= Start && EndDate2 <= End)
                    {
                        var temp = new VwStudentFee();
                        temp.ID = long.Parse(data.Rows[i]["ID"].ToString());
                        temp.ClassType = int.Parse(data.Rows[i]["ClassType"].ToString());
                        temp.TypeOfFee = int.Parse(data.Rows[i]["TypeOfFee"].ToString());
                        temp.Name = data.Rows[i]["Name"].ToString();
                        var status = int.Parse(data.Rows[i]["StudentStatus"].ToString());
                        if (status == 10)
                        {
                            var dateOff = DateTime.MinValue;
                            if (data.Rows[i]["DateOfOff"] != DBNull.Value)
                                dateOff = DateTime.Parse(data.Rows[i]["DateOfOff"].ToString());
                            temp.Name += " (Nghỉ luôn " + dateOff.ToShortDateString() + ")";
                        }
                        else if (status == 2)
                        {
                            var dateOfReserved = DateTime.MinValue;
                            if (data.Rows[i]["DateOfReserved"] != DBNull.Value)
                                dateOfReserved = DateTime.Parse(data.Rows[i]["DateOfReserved"].ToString());
                            temp.Name += " (Bảo lưu " + dateOfReserved.ToShortDateString() + ")";
                        }
                        else if (status == 9)
                        {
                            var dateOfCompleted = DateTime.MinValue;
                            if (data.Rows[i]["DateOfCompleted"] != DBNull.Value)
                                dateOfCompleted = DateTime.Parse(data.Rows[i]["DateOfCompleted"].ToString());
                            temp.Name += " (Đã tốt nghiệp " + dateOfCompleted.ToShortDateString() + ")";
                        }

                        temp.StartDate = StartDate2;
                        temp.EndDate = EndDate2;
                        temp.ClassName = data.Rows[i]["ClassName"].ToString();
                        temp.CourseName = data.Rows[i]["CourseName"].ToString();
                        temp.BranchName = data.Rows[i]["BranchName"].ToString();
                        temp.MoneyPay = int.Parse(data.Rows[i]["Money2"].ToString()); //tien phai dong

                        //lay phieu thu hoc phi
                        var receiptFee = RegisterService.RegisterServices.ReceiptVoucherFeeService.GetByStudentID(temp.ID);
                        if (receiptFee != null && receiptFee.Count > 0)
                        {
                            temp.MoneyPaid = receiptFee.Where(c => c.TimeFee.Contains("Lần 2")).Sum(c => c.Money);
                        }

                        if (temp.MoneyPaid >= temp.MoneyPay)
                            temp.MoneyNotPaid = 0;
                        else
                        {
                            temp.MoneyNotPaid = temp.MoneyPay - temp.MoneyPaid;
                        }
                        //nếu trạng thái là nghỉ luôn thì nợ =0
                        if (status == 10)
                        {
                            temp.MoneyNotPaid = 0;
                        }
                        datalist.Add(temp);

                    }
                    if (StartDate3 >= Start && EndDate3 <= End)
                    {
                        var temp = new VwStudentFee();
                        temp.ID = long.Parse(data.Rows[i]["ID"].ToString());
                        temp.ClassType = int.Parse(data.Rows[i]["ClassType"].ToString());
                        temp.TypeOfFee = int.Parse(data.Rows[i]["TypeOfFee"].ToString());
                        temp.Name = data.Rows[i]["Name"].ToString();
                        var status = int.Parse(data.Rows[i]["StudentStatus"].ToString());
                        if (status == 10)
                        {
                            var dateOff = DateTime.MinValue;
                            if (data.Rows[i]["DateOfOff"] != DBNull.Value)
                                dateOff = DateTime.Parse(data.Rows[i]["DateOfOff"].ToString());
                            temp.Name += " (Nghỉ luôn " + dateOff.ToShortDateString() + ")";
                        }
                        else if (status == 2)
                        {
                            var dateOfReserved = DateTime.MinValue;
                            if (data.Rows[i]["DateOfReserved"] != DBNull.Value)
                                dateOfReserved = DateTime.Parse(data.Rows[i]["DateOfReserved"].ToString());
                            temp.Name += " (Bảo lưu " + dateOfReserved.ToShortDateString() + ")";
                        }
                        else if (status == 9)
                        {
                            var dateOfCompleted = DateTime.MinValue;
                            if (data.Rows[i]["DateOfCompleted"] != DBNull.Value)
                                dateOfCompleted = DateTime.Parse(data.Rows[i]["DateOfCompleted"].ToString());
                            temp.Name += " (Đã tốt nghiệp " + dateOfCompleted.ToShortDateString() + ")";
                        }
                        temp.StartDate = StartDate3;
                        temp.EndDate = EndDate3;
                        temp.ClassName = data.Rows[i]["ClassName"].ToString();
                        temp.CourseName = data.Rows[i]["CourseName"].ToString();
                        temp.BranchName = data.Rows[i]["BranchName"].ToString();
                        temp.MoneyPay = int.Parse(data.Rows[i]["Money3"].ToString()); //tien phai dong

                        //lay phieu thu hoc phi
                        var receiptFee = RegisterService.RegisterServices.ReceiptVoucherFeeService.GetByStudentID(temp.ID);
                        if (receiptFee != null && receiptFee.Count > 0)
                        {
                            temp.MoneyPaid = receiptFee.Where(c => c.TimeFee.Contains("Lần 3")).Sum(c => c.Money);
                        }

                        if (temp.MoneyPaid >= temp.MoneyPay)
                            temp.MoneyNotPaid = 0;
                        else
                        {
                            temp.MoneyNotPaid = temp.MoneyPay - temp.MoneyPaid;
                        }
                        //nếu trạng thái là nghỉ luôn thì nợ =0
                        if (status == 10)
                        {
                            temp.MoneyNotPaid = 0;
                        }
                        datalist.Add(temp);

                    }
                    if (StartDate4 >= Start && EndDate4 <= End)
                    {
                        var temp = new VwStudentFee();
                        temp.ID = long.Parse(data.Rows[i]["ID"].ToString());
                        temp.ClassType = int.Parse(data.Rows[i]["ClassType"].ToString());
                        temp.TypeOfFee = int.Parse(data.Rows[i]["TypeOfFee"].ToString());
                        temp.Name = data.Rows[i]["Name"].ToString();
                        var status = int.Parse(data.Rows[i]["StudentStatus"].ToString());
                        if (status == 10)
                        {
                            var dateOff = DateTime.MinValue;
                            if (data.Rows[i]["DateOfOff"] != DBNull.Value)
                                dateOff = DateTime.Parse(data.Rows[i]["DateOfOff"].ToString());
                            temp.Name += " (Nghỉ luôn " + dateOff.ToShortDateString() + ")";
                        }
                        else if (status == 2)
                        {
                            var dateOfReserved = DateTime.MinValue;
                            if (data.Rows[i]["DateOfReserved"] != DBNull.Value)
                                dateOfReserved = DateTime.Parse(data.Rows[i]["DateOfReserved"].ToString());
                            temp.Name += " (Bảo lưu " + dateOfReserved.ToShortDateString() + ")";
                        }
                        else if (status == 9)
                        {
                            var dateOfCompleted = DateTime.MinValue;
                            if (data.Rows[i]["DateOfCompleted"] != DBNull.Value)
                                dateOfCompleted = DateTime.Parse(data.Rows[i]["DateOfCompleted"].ToString());
                            temp.Name += " (Đã tốt nghiệp " + dateOfCompleted.ToShortDateString() + ")";
                        }
                        temp.StartDate = StartDate4;
                        temp.EndDate = EndDate4;
                        temp.ClassName = data.Rows[i]["ClassName"].ToString();
                        temp.CourseName = data.Rows[i]["CourseName"].ToString();
                        temp.BranchName = data.Rows[i]["BranchName"].ToString();
                        temp.MoneyPay = int.Parse(data.Rows[i]["Money4"].ToString()); //tien phai dong

                        //lay phieu thu hoc phi
                        var receiptFee = RegisterService.RegisterServices.ReceiptVoucherFeeService.GetByStudentID(temp.ID);
                        if (receiptFee != null && receiptFee.Count > 0)
                        {
                            temp.MoneyPaid = receiptFee.Where(c => c.TimeFee.Contains("Lần 4")).Sum(c => c.Money);
                        }

                        if (temp.MoneyPaid >= temp.MoneyPay)
                            temp.MoneyNotPaid = 0;
                        else
                        {
                            temp.MoneyNotPaid = temp.MoneyPay - temp.MoneyPaid;
                        }
                        //nếu trạng thái là nghỉ luôn thì nợ =0
                        if (status == 10)
                        {
                            temp.MoneyNotPaid = 0;
                        }
                        datalist.Add(temp);

                    }
                    if (StartDate5 >= Start && EndDate5 <= End)
                    {
                        var temp = new VwStudentFee();
                        temp.ID = long.Parse(data.Rows[i]["ID"].ToString());
                        temp.ClassType = int.Parse(data.Rows[i]["ClassType"].ToString());
                        temp.TypeOfFee = int.Parse(data.Rows[i]["TypeOfFee"].ToString());
                        temp.Name = data.Rows[i]["Name"].ToString();
                        var status = int.Parse(data.Rows[i]["StudentStatus"].ToString());
                        if (status == 10)
                        {
                            var dateOff = DateTime.MinValue;
                            if (data.Rows[i]["DateOfOff"] != DBNull.Value)
                                dateOff = DateTime.Parse(data.Rows[i]["DateOfOff"].ToString());
                            temp.Name += " (Nghỉ luôn " + dateOff.ToShortDateString() + ")";
                        }
                        else if (status == 2)
                        {
                            var dateOfReserved = DateTime.MinValue;
                            if (data.Rows[i]["DateOfReserved"] != DBNull.Value)
                                dateOfReserved = DateTime.Parse(data.Rows[i]["DateOfReserved"].ToString());
                            temp.Name += " (Bảo lưu " + dateOfReserved.ToShortDateString() + ")";
                        }
                        else if (status == 9)
                        {
                            var dateOfCompleted = DateTime.MinValue;
                            if (data.Rows[i]["DateOfCompleted"] != DBNull.Value)
                                dateOfCompleted = DateTime.Parse(data.Rows[i]["DateOfCompleted"].ToString());
                            temp.Name += " (Đã tốt nghiệp " + dateOfCompleted.ToShortDateString() + ")";
                        }
                        temp.StartDate = StartDate5;
                        temp.EndDate = EndDate5;
                        temp.ClassName = data.Rows[i]["ClassName"].ToString();
                        temp.CourseName = data.Rows[i]["CourseName"].ToString();
                        temp.BranchName = data.Rows[i]["BranchName"].ToString();
                        temp.MoneyPay = int.Parse(data.Rows[i]["Money5"].ToString()); //tien phai dong

                        //lay phieu thu hoc phi
                        var receiptFee = RegisterService.RegisterServices.ReceiptVoucherFeeService.GetByStudentID(temp.ID);
                        if (receiptFee != null && receiptFee.Count > 0)
                        {
                            temp.MoneyPaid = receiptFee.Where(c => c.TimeFee.Contains("Lần 5")).Sum(c => c.Money);
                        }

                        if (temp.MoneyPaid >= temp.MoneyPay)
                            temp.MoneyNotPaid = 0;
                        else
                        {
                            temp.MoneyNotPaid = temp.MoneyPay - temp.MoneyPaid;
                        }
                        //nếu trạng thái là nghỉ luôn thì nợ =0
                        if (status == 10)
                        {
                            temp.MoneyNotPaid = 0;
                        }
                        datalist.Add(temp);

                    }
                    if (StartDate6 >= Start && EndDate6 <= End)
                    {
                        var temp = new VwStudentFee();
                        temp.ID = long.Parse(data.Rows[i]["ID"].ToString());
                        temp.ClassType = int.Parse(data.Rows[i]["ClassType"].ToString());
                        temp.TypeOfFee = int.Parse(data.Rows[i]["TypeOfFee"].ToString());
                        temp.Name = data.Rows[i]["Name"].ToString();
                        var status = int.Parse(data.Rows[i]["StudentStatus"].ToString());
                        if (status == 10)
                        {
                            var dateOff = DateTime.MinValue;
                            if (data.Rows[i]["DateOfOff"] != DBNull.Value)
                                dateOff = DateTime.Parse(data.Rows[i]["DateOfOff"].ToString());
                            temp.Name += " (Nghỉ luôn " + dateOff.ToShortDateString() + ")";
                        }
                        else if (status == 2)
                        {
                            var dateOfReserved = DateTime.MinValue;
                            if (data.Rows[i]["DateOfReserved"] != DBNull.Value)
                                dateOfReserved = DateTime.Parse(data.Rows[i]["DateOfReserved"].ToString());
                            temp.Name += " (Bảo lưu " + dateOfReserved.ToShortDateString() + ")";
                        }
                        else if (status == 9)
                        {
                            var dateOfCompleted = DateTime.MinValue;
                            if (data.Rows[i]["DateOfCompleted"] != DBNull.Value)
                                dateOfCompleted = DateTime.Parse(data.Rows[i]["DateOfCompleted"].ToString());
                            temp.Name += " (Đã tốt nghiệp " + dateOfCompleted.ToShortDateString() + ")";
                        }
                        temp.StartDate = StartDate6;
                        temp.EndDate = EndDate6;
                        temp.ClassName = data.Rows[i]["ClassName"].ToString();
                        temp.CourseName = data.Rows[i]["CourseName"].ToString();
                        temp.BranchName = data.Rows[i]["BranchName"].ToString();
                        temp.MoneyPay = int.Parse(data.Rows[i]["Money6"].ToString()); //tien phai dong

                        //lay phieu thu hoc phi
                        var receiptFee = RegisterService.RegisterServices.ReceiptVoucherFeeService.GetByStudentID(temp.ID);
                        if (receiptFee != null && receiptFee.Count > 0)
                        {
                            temp.MoneyPaid = receiptFee.Where(c => c.TimeFee.Contains("Lần 6")).Sum(c => c.Money);
                        }

                        if (temp.MoneyPaid >= temp.MoneyPay)
                            temp.MoneyNotPaid = 0;
                        else
                        {
                            temp.MoneyNotPaid = temp.MoneyPay - temp.MoneyPaid;
                        }

                        //nếu trạng thái là nghỉ luôn thì nợ =0
                        if (status == 10)
                        {
                            temp.MoneyNotPaid = 0;
                        }

                        datalist.Add(temp);

                    }
                    #endregion


                }
                if (datalist.Count > 0)
                {
                    result = datalist.ConvertToDataTable(datalist);
                    totalHPdathu = datalist.Sum(c => c.MoneyPaid);
                    totalHPphaithu = datalist.Sum(c => c.MoneyPay);
                    totalHPNo = datalist.Sum(c => c.MoneyNotPaid);
                }
          
            }
            return result;

        }

    }


}
